package com.aluvfy.jdbc;
import com.aluvfy.jdbc.utils.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/*使用JDBC实现员工信息管理*/
public class JDBCTest22 {
    public static void main(String[] args) {
        System.out.println("欢迎使用员工信息管理，请认真阅读使用说明！");
        System.out.println("本系统的主要功能包括：查看员工列表、查看某个员工信息、新增员工、修改员工、删除员工");
        System.out.println("请输入对应的功能编号，选择功能：");
        System.out.println("1.查看员工列表");
        System.out.println("2.查看某个员工信息");
        System.out.println("3.新增员工");
        System.out.println("4.修改员工");
        System.out.println("5.删除员工");
        System.out.println("0.退出系统");

        Scanner scanner = new Scanner(System.in);
        while (true) {
            System.out.print("请输入功能编号：");
            int no = scanner.nextInt();
            if (no == 0) {
                System.out.println("欢迎下次使用！");
                break;
            } else if (no == 1) {
                //查看员工列表
                doList();
            } else if (no == 2) {
                //查看某个员工信息
                doList();
                System.out.println("请输入要查看的员工编号：");
                long id = scanner.nextLong();
                doDetail(id);
            } else if (no == 3) {
                //新增员工
                doAdd();
            } else if (no == 4) {
                //修改员工
                doModify();
            } else if (no == 5) {
                //删除员工
                doDelete();
            } else {
                System.out.println("输入的功能编号有误，请重新输入！");
            }
        }

    }

    private static void doDelete() {
        doList();
        System.out.println("请输入要删除的员工编号：");
        Scanner scanner = new Scanner(System.in);
        long id = scanner.nextLong();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from t_employee where id=?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ps.executeUpdate();
            System.out.println("删除"+ id +"员工成功！");
            doList();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }

    }

    private static void doModify() {
        doList();
        System.out.println("请输入您要修改的员工编号：");
        Scanner scanner = new Scanner(System.in);
        long id = scanner.nextLong();

        doDetail(id);
        System.out.println("请输入员工姓名：");
        String name = scanner.next();
        System.out.println("请输入员工职位：");
        String job = scanner.next();
        System.out.println("请输入入职日期：");
        String hiredate = scanner.next();
        System.out.println("请输入员工薪资：");
        double salary = scanner.nextDouble();
        System.out.println("请输入员工住址：");
        String address = scanner.next();
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "update t_employee set name=?, job=?, hiredate=?, salary=?, address=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, job);
            ps.setString(3, hiredate);
            ps.setDouble(4, salary);
            ps.setString(5, address);
            ps.setLong(6, id);
            ps.executeUpdate();
            System.out.println("修改"+ name +"员工的信息成功！");
            doDetail(id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }


    }

    private static void doAdd() {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入员工姓名：");
        String name = scanner.next();
        System.out.println("请输入员工职位：");
        String job = scanner.next();
        System.out.println("请输入入职日期：");
        String hiredate = scanner.next();
        System.out.println("请输入员工薪资：");
        Double salary = scanner.nextDouble();
        System.out.println("请输入员工住址：");
        String address = scanner.next();

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "insert into t_employee(name,job,hiredate,salary,address) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, job);
            ps.setString(3, hiredate);
            ps.setDouble(4, salary);
            ps.setString(5, address);
            ps.executeUpdate();
            System.out.println("新增员工"+ name +"成功！");
            doList();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }

    private static void doDetail(long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "select * from t_employee where id=?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            System.out.println("员工["+ id +"]的详细信息如下");
            while (rs.next()) {
                long id1 = rs.getLong("id");
                String name = rs.getString("name");
                String job = rs.getString("job");
                String hiredate = rs.getString("hiredate");
                String salary = rs.getString("salary");
                String address = rs.getString("address");
                System.out.println("id1 = " + id1);
                System.out.println("name = " + name);
                System.out.println("job = " + job);
                System.out.println("hiredate = " + hiredate);
                System.out.println("salary = " + salary);
                System.out.println("address = " + address);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }
    }

    private static void doList() {
        System.out.println("员工信息列表如下：");

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "select id,name,job from t_employee";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            System.out.println("id\tname\tjob");
            System.out.println("--------------------------------");
            while (rs.next()) {
                System.out.println(rs.getLong("id") + "\t" + rs.getString("name") + "\t" + rs.getString("job"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }


    }
}


